package com.surfacing.rcp.client.util;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Locale;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;


import org.apache.log4j.Logger;

import com.surfacing.rcp.model.entity.StatAnforderung;
import com.surfacing.rcp.model.entity.StatDienststelle;
import com.surfacing.rcp.model.entity.StatDoctor;
import com.surfacing.rcp.model.entity.StatEinsatzart;
import com.surfacing.rcp.model.entity.StatNaca;
import com.surfacing.rcp.model.entity.StatNfs;
import com.surfacing.rcp.model.entity.StatTransportziel;

public class ExcelGenerator {

	private final Logger log = Logger.getLogger(ExcelGenerator.class);
	private WritableWorkbook workbook;
	private WritableSheet nfsSheet;
	private WritableSheet docSheet;
	private WritableSheet anforderungSheet;
	private WritableSheet dienststelleSheet;
	private WritableSheet einsatzArtSheet;
	private WritableSheet tpzSheet;
	private WritableSheet nacaSheet;

	public WritableWorkbook createFile() {
		try {
			String filename = "C:\\NEF\\Statistik.xls";

			File fileTemp = new File(filename);
			if (fileTemp.exists()) {
				fileTemp.delete();
			}

			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("de", "DE"));
			workbook = Workbook.createWorkbook(new File(filename), ws);

			int i = 0;

			nfsSheet = workbook.createSheet("NFS", i++);
			docSheet = workbook.createSheet("Aerzte", i++);
			anforderungSheet = workbook.createSheet("Anforderung", i++);
			dienststelleSheet = workbook.createSheet("Dienststelle", i++);
			einsatzArtSheet = workbook.createSheet("Einsatzart", i++);
			tpzSheet = workbook.createSheet("Transportziel", i++);
			nacaSheet = workbook.createSheet("NACA", i++);

			return workbook;
		} catch (IOException e) {
			log.error("CreateFile Exception: ", e);
		}
		return workbook;
	}

	public void close(WritableWorkbook workbook) {
		try {
			workbook.write();
			workbook.close();
		} catch (IOException e) {
			log.error("Eception: ", e);
		} catch (WriteException e) {
			log.error("Eception: ", e);
		}
	}

	private void createSum(WritableSheet sheet, int rowCount)
			throws RowsExceededException, WriteException {
		WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
				WritableFont.BOLD);
		WritableCellFormat cf = new WritableCellFormat(wf);
		int resultNr = rowCount + 2;
		Label label;
		int c = 0;

		if (rowCount != 0) {

			{
				label = new Label(c++, resultNr, "Summe:", cf);
				sheet.addCell(label);
			}
			{

				Formula sum = new Formula(c++, resultNr, "SUMME(b2:b"
						+ (rowCount + 2) + ")");
				sheet.addCell(sum);
			}
		}
	}

	public void createNfs(WritableSheet nfs, List<StatNfs> ma) {
		/* Format the Font */
		try {
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);
			WritableCellFormat cf = new WritableCellFormat(wf);
			cf.setWrap(true);
			Label label;
			int c = 0;
			int r = 0;

			/* Creates Label and writes date to one cell of sheet */
			{
				label = new Label(c++, r, "Name", cf);
				nfs.addCell(label);
			}
			{
				label = new Label(c++, r, "#", cf);
				nfs.addCell(label);
			}
			for (StatNfs stat : ma) {
				r++;
				{
					label = new Label(0, r, stat.getName());
					nfs.addCell(label);
				}
				{
					Number num = new Number(1, r, Double.parseDouble(stat
							.getAnzahl()));
					nfs.addCell(num);
				}
			}

			createSum(nfs, r);

		} catch (WriteException e) {
			log.error("Eception: ", e);
		}

	}

	public void createDoc(WritableSheet nfs, List<StatDoctor> ma) {
		/* Format the Font */
		try {
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);
			WritableCellFormat cf = new WritableCellFormat(wf);
			cf.setWrap(true);
			Label label;
			int c = 0;
			int r = 0;

			/* Creates Label and writes date to one cell of sheet */
			{
				label = new Label(c++, r, "Name", cf);
				nfs.addCell(label);
			}
			{
				label = new Label(c++, r, "#", cf);
				nfs.addCell(label);
			}
			for (StatDoctor stat : ma) {
				r++;
				{
					label = new Label(0, r, stat.getName());
					nfs.addCell(label);
				}
				{
					Number num = new Number(1, r, Double.parseDouble(stat
							.getAnzahl()));
					nfs.addCell(num);
				}
			}

			createSum(nfs, r);
		} catch (WriteException e) {
			log.error("Eception: ", e);
		}
	}

	public void createAnforderung(WritableSheet nfs, List<StatAnforderung> ma) {
		/* Format the Font */
		try {
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);
			WritableCellFormat cf = new WritableCellFormat(wf);
			cf.setWrap(true);
			Label label;
			int c = 0;
			int r = 0;

			/* Creates Label and writes date to one cell of sheet */
			{
				label = new Label(c++, r, "Name", cf);
				nfs.addCell(label);
			}
			{
				label = new Label(c++, r, "#", cf);
				nfs.addCell(label);
			}
			for (StatAnforderung stat : ma) {
				r++;
				{
					label = new Label(0, r, stat.getName());
					nfs.addCell(label);
				}
				{
					Number num = new Number(1, r, Double.parseDouble(stat
							.getAnzahl()));
					nfs.addCell(num);
				}
			}

			createSum(nfs, r);
		} catch (WriteException e) {
			log.error("Eception: ", e);
		}
	}

	public void createDienststelle(WritableSheet nfs, List<StatDienststelle> ma) {
		/* Format the Font */
		try {
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);
			WritableCellFormat cf = new WritableCellFormat(wf);
			cf.setWrap(true);
			Label label;
			int c = 0;
			int r = 0;

			/* Creates Label and writes date to one cell of sheet */
			{
				label = new Label(c++, r, "Name", cf);
				nfs.addCell(label);
			}
			{
				label = new Label(c++, r, "#", cf);
				nfs.addCell(label);
			}
			for (StatDienststelle stat : ma) {
				r++;
				{
					label = new Label(0, r, stat.getName());
					nfs.addCell(label);
				}
				{
					Number num = new Number(1, r, Double.parseDouble(stat
							.getAnzahl()));
					nfs.addCell(num);
				}
			}

			createSum(nfs, r);
		} catch (WriteException e) {
			log.error("Eception: ", e);
		}
	}

	public void createEinsatzart(WritableSheet nfs, List<StatEinsatzart> ma) {
		/* Format the Font */
		try {
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);
			WritableCellFormat cf = new WritableCellFormat(wf);
			cf.setWrap(true);
			Label label;
			int c = 0;
			int r = 0;

			/* Creates Label and writes date to one cell of sheet */
			{
				label = new Label(c++, r, "Name", cf);
				nfs.addCell(label);
			}
			{
				label = new Label(c++, r, "#", cf);
				nfs.addCell(label);
			}
			for (StatEinsatzart stat : ma) {
				r++;
				{
					label = new Label(0, r, stat.getName());
					nfs.addCell(label);
				}
				{
					Number num = new Number(1, r, Double.parseDouble(stat
							.getAnzahl()));
					nfs.addCell(num);
				}
			}

			createSum(nfs, r);
		} catch (WriteException e) {
			log.error("Eception: ", e);
		}
	}

	public void createTransportziel(WritableSheet nfs,
			List<StatTransportziel> ma) {
		/* Format the Font */
		try {
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);
			WritableCellFormat cf = new WritableCellFormat(wf);
			cf.setWrap(true);
			Label label;
			int c = 0;
			int r = 0;

			/* Creates Label and writes date to one cell of sheet */
			{
				label = new Label(c++, r, "Name", cf);
				nfs.addCell(label);
			}
			{
				label = new Label(c++, r, "#", cf);
				nfs.addCell(label);
			}
			for (StatTransportziel stat : ma) {
				r++;
				{
					label = new Label(0, r, stat.getName());
					nfs.addCell(label);
				}
				{
					Number num = new Number(1, r, Double.parseDouble(stat
							.getAnzahl()));
					nfs.addCell(num);
				}
			}

			createSum(nfs, r);
		} catch (WriteException e) {
			log.error("Eception: ", e);
		}
	}

	public void createNaca(WritableSheet nfs, List<StatNaca> ma) {
		/* Format the Font */
		try {
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);
			WritableCellFormat cf = new WritableCellFormat(wf);
			cf.setWrap(true);
			Label label;
			int c = 0;
			int r = 0;

			/* Creates Label and writes date to one cell of sheet */
			{
				label = new Label(c++, r, "Name", cf);
				nfs.addCell(label);
			}
			{
				label = new Label(c++, r, "#", cf);
				nfs.addCell(label);
			}
			for (StatNaca stat : ma) {
				r++;
				{
					label = new Label(0, r, stat.getName());
					nfs.addCell(label);
				}
				{
					Number num = new Number(1, r, Double.parseDouble(stat
							.getAnzahl()));
					nfs.addCell(num);
				}
			}

			createSum(nfs, r);
		} catch (WriteException e) {
			log.error("Eception: ", e);
		}
	}

}